<?php

require_once("db.inc.php");
require_once("common.inc.php");
require_once("queries.inc.php");

auth();
$dbh = get_db();

initSession();

xmlhead("WikiVocabularyTrainer - Wordgroup management");

?>
<body>

<script language="JavaScript">
function explain_checkbox() {
	javascript:alert("if you enable the checkbox, then the search will also include words from languages other than the target language. For example, if you enable this, you can search for 'Sunday' and find the translation in your target language, if you have forgotten what it was. If you do know the word you are looking for then you don't need to enable this.");
}
</script>

<?php

$pagevars = array();

$languages = getLanguages();

//////////////////////// Maintenance based on POST variables //////////////////////

//enable/disable group for testing?
if (
	isset($_POST['selgroup']) && is_numeric($_POST['selgroup'])
	&&
	isset($_POST['set_enabled']) && is_numeric($_POST['set_enabled'])
	) {
	if ($_POST['set_enabled'] > 0) {
		mysql_query(
			"UPDATE wvt_wordgroup_subscriptions
			SET enabled = 1
			WHERE user_id = {$_SESSION['user_id']}
			AND wordgroup_id = {$_POST['selgroup']}
			", $dbh
		) or die ("Can't enable wordgroup {$_POST['selgroup']} for user {$_SESSION['user_id']}: ". mysql_error());
	} else {
		mysql_query(
			"UPDATE wvt_wordgroup_subscriptions
			SET enabled = 0
			WHERE user_id = {$_SESSION['user_id']}
			AND wordgroup_id = {$_POST['selgroup']}
			", $dbh
		) or die ("Can't disable wordgroup {$_POST['selgroup']} for user {$_SESSION['user_id']}: ". mysql_error());
	}
}

//add submitted syntrans(es) to a group?
if (!isset($_POST['selgroup']) || !is_numeric($_POST['selgroup']) || $_POST['selgroup'] < 1) {
	$pagevars['selgroup'] = -1;
} else {
	$pagevars['selgroup'] = $_POST['selgroup'];
	
	$query = mysql_query("
		SELECT enabled
		FROM wvt_wordgroup_subscriptions
		WHERE user_id = {$_SESSION['user_id']}
		AND wordgroup_id = {$pagevars['selgroup']}
		"
	) or die ("Can't get subscription status for user {$_SESSION['user_id']} on group {$pagevars['selgroup']}: " . mysql_error());
	
	$row = mysql_fetch_array($query);
	if ($row['enabled'] > 0) {
		$pagevars['group_enabled'] = true;
	} else {
		$pagevars['group_enabled'] = false;
	}
	
	if (isset($_POST['addsyntrans']) && is_numeric($_POST['addsyntrans']) && $_POST['addsyntrans'] > 0) {
		$query = mysql_query("
			SELECT *
			FROM uw_syntrans s, uw_expression e
			WHERE s.syntrans_sid = {$_POST['addsyntrans']}
			AND s.expression_id = e.expression_id
			AND s.identical_meaning = 1
			AND s.remove_transaction_id IS NULL
			AND e.remove_transaction_id IS NULL
			", $dbh
		) or die ("Can't add syntrans; didn't pass (apparently not so) paranoid security check. The error was: " . mysql_error());
		mysql_query(
			"REPLACE INTO wvt_wordgroup_syntranses
			VALUES (
				{$_POST['addsyntrans']},
				{$_POST['selgroup']}
			)",
			$dbh
		)
		or die("can not add syntrans to group:" . mysql_error());
	}
}

//rename a group?

if (isset($_POST['rengroup']) && is_numeric($_POST['rengroup']) && $_POST['rengroup'] > 0) {
	if (!isset($_POST['confirmgrouprename'])) {
		$query = mysql_query(
			"SELECT *
			FROM wvt_wordgroup_names
			WHERE wordgroup_id = {$_POST['rengroup']}
			",
			$dbh
		) or die ("can not show group before renaming:" . mysql_error());
		$row = mysql_fetch_array($query);
	?>
	Are you sure you want to rename group <?php echo "{$row['name']} ({$_POST['rengroup']}) to {$_POST['renameto']}"; ?> ?
		<br />
<form method="POST">
<input type="hidden" name="rengroup" value="<?php echo $_POST['rengroup']; ?>">
<input type="hidden" name="confirmgrouprename" value="1">
<input type="submit" value="Yes, rename that group!"> <a href="groupman.php">No! Go back!</a>
</form>
</body>
</html>
<?php exit;
	} else if ($_POST['confirmgrouprename'] == 1) {
		$pagevars['selgroup'] = $_POST['rengroup'];
		if (isset($_POST['renameto'])) {
			$groupname_sans_nasties = strip_tags(mysql_real_escape_string($_POST['renameto']), $dbh);
			if ($groupname_sans_nasties != '') {
				mysql_query(
					"UPDATE wvt_wordgroup_names
					SET name = '$groupname_sans_nasties'
					WHERE wordgroup_id = {$_POST['rengroup']}
					", $dbh
				) or die ("can't rename group: " . mysql_error());
			}
		}
	}
}

//delete a group?
if (isset($_POST['delgroup']) && $_POST['delgroup'] > 0) {
	if (!isset($_POST['confirmgroupdelete'])) { //print out group and its members for confirmation
		$query = mysql_query(
			"SELECT *
			FROM wvt_wordgroup_names
			WHERE wordgroup_id = {$_POST['delgroup']}
			",
			$dbh
		) or die ("can not show group before deletion:" . mysql_error());
		
		while ($row = mysql_fetch_array($query)) {
			echo "Are you sure you want to delete group {$row['name']} ({$_POST['delgroup']}), with these members:<br/><br/>\n\n";
		}
		
		$query = mysql_query(
			"SELECT *
			FROM wvt_wordgroup_syntranses ws, uw_syntrans s, uw_expression e
			WHERE
			ws.syntrans_sid = s.syntrans_sid
			AND s.expression_id = e.expression_id
			AND ws.wordgroup_id = {$_POST['delgroup']}
			AND e.remove_transaction_id IS NULL
			AND s.remove_transaction_id IS NULL
			",
			$dbh
		) or die (mysql_error());
		
		if (mysql_num_rows($query) > 0) while ($row = mysql_fetch_array($query)) {
			echo "{$row['spelling']} (<a href=\"/wikidata/index.php?title=DefinedMeaning:({$row['defined_meaning_id']})\">DM:{$row['defined_meaning_id']}</a>)<br/>\n";
		} else {
			echo "(no members)<br />\n";
		}
		
		?>
<br />
<form method="POST">
<input type="hidden" name="delgroup" value="<?php echo $_POST['delgroup']; ?>">
<input type="hidden" name="confirmgroupdelete" value="1">
<input type="submit" value="Yes, delete that group!"> <a href="groupman.php">No! Go back!</a>
</form>
</body>
</html>
<?php
		exit; //done printing the deletion warning

	} else if ($_POST['confirmgroupdelete'] == 1) { //actually delete the group description and its members and subscription for the user (hmm? rething multi-user architecture here)
		mysql_query("DELETE FROM wvt_wordgroup_syntranses WHERE wordgroup_id = {$_POST['delgroup']}", $dbh) or die("can not remove word while deleting group: " . mysql_error());
		mysql_query("DELETE FROM wvt_wordgroup_names WHERE wordgroup_id = {$_POST['delgroup']}", $dbh) or die("can not delete group from table wvt_wordgroup_names: " . mysql_error());
		mysql_query("DELETE FROM wvt_wordgroup_subscriptions WHERE wordgroup_id = {$_POST['delgroup']} AND user_id = {$_SESSION['user_id']}", $dbh) or die("can not delete wordgroup subscriptions for wordgroup {$_POST['delgroup']}: " . mysql_error());
	}
}

//create a new group?
if (isset($_POST['newgroup'])) {
	$groupname_sans_nasties = strip_tags(mysql_real_escape_string($_POST['newgroup']), $dbh);
	if ($groupname_sans_nasties != "") {
		mysql_query(
			"INSERT INTO wvt_wordgroup_names
			VALUES( '0', '$groupname_sans_nasties')
			", $dbh
		) or die("can not insert new group name: " . mysql_error());
		$query = mysql_query(
			"SELECT LAST_INSERT_ID() gid
			FROM wvt_wordgroup_names
			", $dbh
		) or die ("can not get id of last inserted group: " . mysql_error());
		$row = mysql_fetch_array($query);
		$pagevars['selgroup'] = $row['gid'];
		mysql_query("INSERT INTO wvt_wordgroup_subscriptions
		VALUES( {$_SESSION['user_id']}, {$row['gid']}, 0 )
		", $dbh
		) or die("can not add disabled subscription to new group {$row['gid']} for user {$_SESSION['user_id']}: " . mysql_error());
	}
}


//remove a syntrans from a group?
if (
	isset($_POST['delsyntrans']) && is_numeric($_POST['delsyntrans'])
	&&
	isset($_POST['selgroup'])    && is_numeric($_POST['selgroup'])
	) {
	mysql_query(
		"DELETE FROM wvt_wordgroup_syntranses
		WHERE wordgroup_id = {$_POST['selgroup']}
		AND syntrans_sid = {$_POST['delsyntrans']}
		", $dbh
	) or die("Can't delete syntrans {$_POST['delsyntrans']} from group {$_POST['selgroup']}: ". mysql_error());
}

//////////////////////// End maintenance ///////////////////////////////

//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\//\\

////////////// Update session according to POST values /////////////////

//this could go into $pagevars instead, but this way the preference is stored

if (isset($_POST['all_languages']) && $_POST['all_languages'] === 'on') {
	$_SESSION['all_languages'] = true;
} else {
	$_SESSION['all_languages'] = false;
}

if (isset($_POST['only_member_dms']) && $_POST['only_member_dms'] === 'on') {
	$_SESSION['only_member_dms'] = true;
} else {
	$_SESSION['only_member_dms'] = false;
}

///////////////////////// Done with POST values ////////////////////////


?>
<form method="post">
<?php

$wordgroup_names = array();
$wordgroup_sizes = array();
$wordgroup_dropdown = array();

$query = mysql_query(
	"SELECT wn.*, ws.wordgroup_id null_if_empty, COUNT(*) num
	FROM wvt_wordgroup_names wn LEFT JOIN wvt_wordgroup_syntranses ws ON wn.wordgroup_id = ws.wordgroup_id
	WHERE wn.wordgroup_id > 0
	GROUP BY wn.wordgroup_id
	ORDER BY name ASC", $dbh
) or die(mysql_error());

while ($row = mysql_fetch_array($query)) {
	$numtext = "";

	if (is_null($row['null_if_empty'])) {
		$wordgroup_sizes[$row['wordgroup_id']] = 0;
		$numtext = "empty";
	} else {
		$wordgroup_sizes[$row['wordgroup_id']] = $row['num'];
		$numtext = $row['num'];
	}
	$wordgroup_names[$row['wordgroup_id']] = $row['name'];
	$wordgroup_dropdown[$row['wordgroup_id']] = "{$row['name']} ($numtext)";
	
}

dropdown("selgroup", $pagevars['selgroup'], "Select a group:", $wordgroup_dropdown, true);

?>
</select> 
<input type="submit" value="Select">
 or create new group: <input name="newgroup" size="32" maxlength="255" /> <input type="submit" value="Create" />
</form><br />
<?php

if ($pagevars['selgroup'] > 0) {

	$wordgroup_members = get_wordgroup_members($pagevars['selgroup']); //a list of syntranses already in the group

	?>

<form method="post">
<input type="hidden" name="selgroup" value="<?php echo $pagevars['selgroup']; ?>">
<input type="radio" name="set_enabled" value="0"<?php if (!$pagevars['group_enabled']) echo " checked=\"checked\""; ?>> Disabled 
<input type="radio" name="set_enabled" value="1"<?php if ($pagevars['group_enabled']) echo " checked=\"checked\""; ?>> Enabled 
<input type="submit" value="Change">
</form><br />

<form method="post">
<input type="hidden" name="rengroup" value="<?php echo $pagevars['selgroup']; ?>">
<input type="submit" value="Rename the '<?php echo $wordgroup_names[$pagevars['selgroup']]; ?>' group to:">
<input name="renameto" size="32" maxlength="255" />
</form><br />

<form method="post">
<input type="hidden" name="delgroup" value="<?php echo $pagevars['selgroup']; ?>">
<input type="submit" value="Delete the '<?php echo $wordgroup_names[$pagevars['selgroup']]; ?>' group">
</form><br />

<hr /><br />

<form method="post" name="freesearch">
<input type="hidden" name="selgroup" value="<?php echo $pagevars['selgroup']; ?>">
Add a new syntrans to the <?php echo $wordgroup_names[$pagevars['selgroup']]; ?> group: <br />

<?php
	//Free search
	//TODO: Completely overhaul and use AJAX
	
	if (isset($_POST['freesearch'])) { //show result set
		$search_string = mysql_real_escape_string($_POST['freesearch']);
		
		$matching_dms = array();
		$search_results = array();
		
		if ($search_string === "") {
			if ($_SESSION['only_member_dms']) {
				$spelling_sql = "1";
			} else {
				$spelling_sql = "spelling = ''";
			}
		} else if (strlen($search_string) < 3) {
			$spelling_sql = "spelling = '$search_string'";
		} else {
			$spelling_sql = "spelling like '%$search_string%'";
		}
		
		if ($_SESSION['all_languages']) {
			$language_restriction_sql = "";
		} else {
			$language_restriction_sql = "AND language_id = {$_SESSION['learn_language']}";
		}
		
		//first find all expressions matching the search
		//if all_languages is on, also find "foreign" words
		//if the only_member_dms flag is set, only use those dm's that are present in the wordgroup
		
		if ($_SESSION['only_member_dms']) {
			$query = mysql_query(
					"SELECT DISTINCT s.defined_meaning_id
					FROM uw_expression e, uw_syntrans s, uw_defined_meaning dm, uw_syntrans swg, wvt_wordgroup_syntranses wgs
					WHERE $spelling_sql
					$language_restriction_sql
					AND e.expression_id = s.expression_id
					AND s.identical_meaning = 1
					AND dm.defined_meaning_id = s.defined_meaning_id
					AND swg.defined_meaning_id = dm.defined_meaning_id
					AND swg.syntrans_sid = wgs.syntrans_sid
					AND wgs.wordgroup_id = {$pagevars['selgroup']}
					AND e.remove_transaction_id IS NULL
					AND s.remove_transaction_id IS NULL
					AND dm.remove_transaction_id IS NULL
					AND swg.remove_transaction_id IS NULL
				", $dbh
			) or die (mysql_error());
		} else {
			$query = mysql_query(
					"SELECT DISTINCT s.defined_meaning_id
					FROM uw_expression e, uw_syntrans s
					WHERE $spelling_sql
					$language_restriction_sql
					AND e.expression_id = s.expression_id
					AND identical_meaning = 1
					AND s.remove_transaction_id IS NULL
					AND e.remove_transaction_id IS NULL
				", $dbh
			) or die (mysql_error());
		}
		
		while ($row = mysql_fetch_array($query)) {
			$matching_dms[] = $row['defined_meaning_id'];
		}
		
		//for all the DM's that were found to contain an expression in the language(s) specified,
		//show the expressions in the selected learn_language associated with them
		foreach ($matching_dms as $dm) {
			$query = mysql_query(
				"SELECT defined_meaning_id, syntrans_sid, language_id, spelling
				FROM uw_expression e, uw_syntrans s
				WHERE language_id = {$_SESSION['learn_language']}
				AND defined_meaning_id = $dm
				AND e.expression_id = s.expression_id
				AND identical_meaning = 1
				AND s.remove_transaction_id IS NULL
				AND e.remove_transaction_id IS NULL
				AND syntrans_sid NOT IN (
					SELECT syntrans_sid
					FROM wvt_wordgroup_syntranses
					WHERE wordgroup_id = {$pagevars['selgroup']}
				)
				ORDER BY syntrans_sid ASC
				", $dbh
			) or die ("error finding syntrans in selgroup language for DM: " . mysql_error());
			
			while (($row = mysql_fetch_array($query)) && count($search_results) < 100) {
				$subquery = mysql_query(
					"SELECT spelling
					FROM uw_expression e, uw_syntrans s
					WHERE defined_meaning_id = {$row['defined_meaning_id']}
					AND s.expression_id = e.expression_id
					AND e.language_id = {$_SESSION['interface_language']}
					AND e.remove_transaction_id IS NULL
					AND s.remove_transaction_id IS NULL
					ORDER BY identical_meaning DESC
					LIMIT 1", $dbh
				) or die ("Error while searching synonym for syntrans: " . mysql_error());
				if (mysql_num_rows($subquery) > 0) {
					$subrow = mysql_fetch_array($subquery);
					$search_results[$row['syntrans_sid']] = "{$row['spelling']} ({$subrow['spelling']})";
				} else {
					$search_results[$row['syntrans_sid']] = "{$row['spelling']} (-)";
				}
			}
		}
		
		$numresults = count($search_results);
		if ($numresults > 0) {
			dropdown("addsyntrans", "", "Search results ($numresults):", $search_results, true);
			?> <input type="submit" value="Add this word"><br /><br />
<?php
		} else {
			?>Search did not return any hits. Edit the <a href="/wikidata/">database</a>?<br/>
<?php
		} 
	}
?>

Find words in <?php dropdown("learn_language", $_SESSION['learn_language'], "Select language", $languages); ?> 
<input type="checkbox" name="all_languages"<?php if ($_SESSION['all_languages']) echo ' checked="checked"'; ?>> Match words in any language (<a href="javascript:explain_checkbox();">explain</a>) 
<input type="checkbox" name="only_member_dms"<?php if ($_SESSION['only_member_dms']) echo ' checked="checked"'; ?>> Only look for translations of words already in this group<br />
<input name="freesearch" size="50" maxlength="50"> <input type="submit" value="Free Search (no wildcards for < 3 chars)"> <br /></form>

<?php
	//print out list of words already in the group
	//display format - [defined meaning text in interface_language or "no translation"]: [expression in learn_language]
	
	if (count($wordgroup_members > 0)) {
		?>Words currently in the <?php echo $wordgroup_names[$pagevars['selgroup']]; ?> group:<br /><br />
		<table>
<?php
		foreach ($wordgroup_members as $wordgroup_member) {
		
			$subquery = mysql_query(
				"SELECT spelling, language_id, defined_meaning_id
				FROM uw_syntrans s, uw_expression e
				WHERE
				s.syntrans_sid = $wordgroup_member
				AND s.expression_id = e.expression_id
				AND s.remove_transaction_id IS NULL
				AND e.remove_transaction_id IS NULL"
			) or die (mysql_error());
			
			?><tr><td class="language"><?php
			if (mysql_num_rows($subquery) > 0) {
				while($subrow = mysql_fetch_array($subquery)) {
					echo $languages[$subrow['language_id']] . "<td>" . $subrow['spelling'];
					$dm_text = get_text_for_dm($subrow['defined_meaning_id']);
					echo "<td>$dm_text (<a href=\"/wikidata/index.php?title=DefinedMeaning:({$subrow['defined_meaning_id']})\">DM</a>)";
				}
			} else {
				echo "-<td>(no expression available) - check syntrans_sid $wordgroup_member!"; //of the expression
			}
		?>
<td>
<form method="post">
<input type="hidden" name="selgroup" value="<?php echo $pagevars['selgroup']; ?>">
<input type="hidden" name="delsyntrans" value="<?php echo $wordgroup_member; ?>">
<input type="submit" value="Delete"></form>
</tr>

<?php
		}
		echo "</table>\n";
	} else {
		echo "The group is empty. Add words using the form above.<br />\n";
	}

} //end if ($pagevars['selgroup'] > 0)

?>
</body>
</html>